# -*- coding: utf-8 -*- 
"""
@Author: 孟颖
@email: 652044581@qq.com
@date: 2023/5/8 10:18
@desc: 
"""

from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl import Workbook
import datetime
import os

excel_container_dir = os.path.dirname(os.path.abspath(__file__))
excel_container_path = os.path.join(excel_container_dir, "excel_dir")
os.makedirs(excel_container_path, exist_ok=True)


class ExcelStyle:

    def __init__(self):
        self.font = Font(name='Calibri', size=11)
        self.fill = PatternFill(fill_type='solid', start_color='FFFFFF', end_color='FFFFFF')
        self.border = Border(left=Side(border_style='thin', color='000000'),
                             right=Side(border_style='thin', color='000000'),
                             top=Side(border_style='thin', color='000000'),
                             bottom=Side(border_style='thin', color='000000'))
        self.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)


class ExcelUtils:
    def __init__(self):
        self.wb = Workbook()
        self.ws = self.wb.create_sheet(title="导出数据", index=0)
        self.style = ExcelStyle()

    def set_row_height(self, row_num, height):
        """
        Sets the height of a row in the worksheet
        """
        self.ws.row_dimensions[row_num].height = height

    def set_column_width(self, col_num, width):
        """
        Sets the width of a column in the worksheet
        """
        col_letter = get_column_letter(col_num)
        self.ws.column_dimensions[col_letter].width = width

    def set_row_style(self, row_num):
        """
        Sets the style of a row in the worksheet
        """
        for cell in self.ws[row_num]:
            if self.style.font:
                cell.font = self.style.font
            if self.style.alignment:
                cell.alignment = self.style.alignment
            if self.style.fill:
                cell.fill = self.style.fill
            if self.style.border:
                cell.border = self.style.border

    def write_data(self, map: dict, data):
        titles = map.keys()
        values = map.values()

        # 写入title
        for i, v in enumerate(titles):
            self.ws.cell(1, i + 1, v)

        # 写入数据
        data_index = 2
        for item in data:
            for i, v in enumerate(values):
                value = item.get(v, "")
                if isinstance(value, datetime.datetime):
                    value = str(value).split('.')[0]
                self.ws.cell(data_index, i + 1, value)

            data_index += 1

    def save(self, filename):
        filepath = os.path.join(excel_container_path, filename)
        self.wb.save(filepath)
        return filepath


if __name__ == '__main__':
    map = {
        "下载流水号": "log_id",
        "门牌号": "log_room_id",
    }

    data = [
        {"log_id": "日志1", "log_room_id": "日志2"},
        {"log_id": "日志3", "log_room_id": "日志4"}
    ]
    ex = ExcelUtils()
    ex.write_data(map, data)
    ex.set_column_width(1, 100)
    ex.set_row_height(1, 100)
    ex.set_row_style(1)
    ex.save(r"C:\Users\my\Desktop\vanke\6.xlsx")
